package org.jinion.database.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.jinion.constants.OperatorEnum;
import org.jinion.database.AbstractDatabase;

/**
 * Database implementation for MySQL.<br>
 * <br>
 * <b>NOTE: NOT thread safe</b>
 * @author zhlmmc
 */
public class DatabaseMySql extends AbstractDatabase {
	/**
	 * Create a Database with the given connection.
	 * 
	 * @param connection
	 */
	public DatabaseMySql(Connection connection) {
		this.connection = connection;
		this.whareClauses = new ArrayList(10);
		// this.connection.createStatement().executeQuery("");
	}

	/**
	 * 
	 * @param tableNames
	 * @param fieldNames
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	protected String buildQuerySql(String[] tableNames, String[] fieldNames,
			String orderBy, OperatorEnum ascOrDesc) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		if (tableNames.length == 1) {
			String tableName = tableNames[0];
			if (fieldNames == null || fieldNames.length == 0) {
				sb.append("SELECT * FROM " + tableName + " ");
			} else {
				sb.append("SELECT ");
				for (int i = 0; i < fieldNames.length; i++) {
					sb.append(fieldNames[i] + ", ");
				}
				sb.delete(sb.length() - 2, sb.length() - 1);
				sb.append("FROM " + tableName + " ");
			}
		} else {
			if (fieldNames == null || fieldNames.length == 0) {
				sb.append("SELECT * FROM ");
			} else {
				sb.append("SELECT ");
				for (int i = 0; i < fieldNames.length; i++) {
					sb.append(fieldNames[i] + ", ");
				}
				sb.delete(sb.length() - 2, sb.length() - 1);
				sb.append("FROM ");
			}
			for (int i = 0; i < tableNames.length; i++) {
				sb.append(tableNames[i] + " " + tableNames[i] + ", ");
			}
			sb.delete(sb.length() - 2, sb.length() - 1);
		}

		// add where clauses
		List[] tableColumns = new List[tableNames.length];
		for (int i = 0; i < tableColumns.length; i++) {
			tableColumns[i] = this.getColumns(tableNames[i]);
		}
		String where = this.getWhereClause(tableColumns);
		if (where != null) {
			sb.append(where);
		}
		whareClauses.clear();
		if (orderBy != null) {
			sb.append("ORDER BY " + orderBy + " " 
					+ OperatorEnum.matchComparator(ascOrDesc));
		}
		return sb.toString();
	}

	/**
	 * Only support single table.
	 * 
	 * @param tableNames
	 * @param fieldNames
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	protected String buildUpdateSql(String[] tableNames, String[] fieldNames,
			Object data) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		if (tableNames.length == 1) {
			if (data == null) {
				return null;
			}
			String tableName = tableNames[0];
			List usefulFields = null;
			List columns = this.getColumns(tableName);
			sb.append("UPDATE " + tableName + " SET ");
			// SET clause
			Map map = (Map) data;
			if (fieldNames == null || fieldNames.length == 0) {
				/* try to get all columns from map */
				usefulFields = columns;
			} else {
				/* Only get specified columns from map */
				List list = new ArrayList();
				for (int i = 0; i < fieldNames.length; i++) {
					list.add(fieldNames[i]);
				}
				usefulFields = list;
			}
			Set keys = map.keySet();
			for (Iterator iter = keys.iterator(); iter.hasNext();) {
				String element = (String) iter.next();
				// filter keys that don't belongs to this table
				if (usefulFields.contains(element)) {
					//String value = map.get(element).toString();
					String value = this.fixChars(map.get(element).toString());
					sb.append(element + " = '" + value + "', ");
				}
				else if(element.startsWith("#")){
					int index = Integer.parseInt(element.substring(1)) - 1;
					String column = columns.get(index).toString();
					if (usefulFields.contains(column)) {
						//String value = map.get(element).toString();
						String value = this.fixChars(map.get(element).toString());
						sb.append(column + " = '" + value + "', ");
					}
				}
			}
			sb = sb.delete(sb.length() - 2, sb.length() - 1);
			// add where clauses
			String where = this.getWhereClause(new List[] { usefulFields });
			if (where != null) {
				sb.append(where);
			}
			whareClauses.clear();
			return sb.toString();
		}
		return null;
	}

	/**
	 * Only support single table.
	 * 
	 * @param tableNames
	 * @param fieldNames
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	protected String buildInsertSql(String[] tableNames, String[] fieldNames,
			Object data) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		if (tableNames.length == 1) {
			if (data == null) {
				return null;
			}
			String tableName = tableNames[0];
			List usefulFields = null;
			List columns = this.getColumns(tableName);
			sb.append("INSERT INTO " + tableName + " ");
			sb.append("(");
			// SET clause
			StringBuilder sb2 = new StringBuilder("VALUES (");
			Map map = (Map) data;
			if (fieldNames == null || fieldNames.length == 0) {
				/* try to get all columns from map */
				usefulFields = columns;
			} else {
				/* Only get specified columns from map */
				List list = new ArrayList();
				for (int i = 0; i < fieldNames.length; i++) {
					list.add(fieldNames[i]);
				}
				usefulFields = list;
			}
			
			Set keys = map.keySet();
			for (Iterator iter = keys.iterator(); iter.hasNext();) {
				String element = (String) iter.next();
				// filter keys that don't belongs to this table
				if (usefulFields.contains(element)) {
					//String value = map.get(element).toString();
					String value = this.fixChars(map.get(element).toString());
					sb.append(element + ", ");
					sb2.append("'" + value + "', ");
				}
				else if(element.startsWith("#")){
					int index = Integer.parseInt(element.substring(1)) - 1;
					String column = columns.get(index).toString();
					if (usefulFields.contains(column)) {
						//String value = map.get(element).toString();
						String value = this.fixChars(map.get(element).toString());
						sb.append(column + ", ");
						sb2.append("'" + value + "', ");
					}
				}
			}
			
			sb.delete(sb.length() - 2, sb.length());
			sb2.delete(sb2.length() - 2, sb2.length() - 1);
			sb.append(") ");
			sb2.append(") ");
			sb.append(sb2.toString());

			// add where clauses
			String where = this.getWhereClause(new List[] { columns });
			if (where != null) {
				sb.append(where);
			}
			this.whareClauses.clear();
			return sb.toString();
		}
		return null;
	}

	/**
	 * Only support single table.
	 * 
	 * @param tableNames
	 * @param fieldNames
	 * @param data
	 * @return
	 * @throws SQLException
	 */
	protected String buildDeleteSql(String tableName) throws SQLException {
		StringBuilder sb = new StringBuilder("");
		sb.append("DELETE FROM " + tableName + " ");

		// add where clauses
		String where = this.getWhereClause(new List[] { this
				.getColumns(tableName) });
		if (where != null) {
			sb.append(where);
		}

		this.whareClauses.clear();

		return sb.toString();
	}

	/**
	 * This medhod assumes that if the string of <i>value</i> is the same with
	 * any column name, <br>
	 * the <i>value</i> denotes the column not the string. If you want to use
	 * the string of the <br>
	 * <i>value</i> instead of the column, add single quotes around <i>value</i>.
	 * 
	 * @param tableColumns
	 * @return
	 */
	private String getWhereClause(List[] tableColumns) {
		StringBuilder sb = new StringBuilder("");

		if (!this.whareClauses.isEmpty()) {
			WhereClause wc = (WhereClause) this.whareClauses.get(0);
			wc.setValue(this.fixChars(wc.getValue().toString()));
			// column value or string value
			if (this.existsInColumns(wc.getValue(), tableColumns)) {
				sb.append("WHERE " + wc.getFieldName() + " "
						+ OperatorEnum.matchComparator(wc.getCompareType()) + " "
						+ wc.getValue() + " ");
			} else {
				sb.append("WHERE " + wc.getFieldName() + " "
						+ OperatorEnum.matchComparator(wc.getCompareType()) + " '"
						+ wc.getValue() + "' ");
			}

			for (int i = 1; i < this.whareClauses.size(); i++) {
				wc = (WhereClause) this.whareClauses.get(i);
				wc.setValue(this.fixChars(wc.getValue().toString()));
				//sb.append(this.getConnectionString(wc.getClauseLogicalType()) + " ");
				sb.append(OperatorEnum.matchComparator(wc.getClauseLogicalType()) + " ");
				// column value or string value
				if (this.existsInColumns(wc.getValue(), tableColumns)) {
					sb.append(wc.getFieldName() + " "
							+ OperatorEnum.matchComparator(wc.getCompareType()) + " "
							+ wc.getValue() + " ");
				} else {
					sb.append(wc.getFieldName() + " "
							+ OperatorEnum.matchComparator(wc.getCompareType()) + " '"
							+ wc.getValue() + "' ");
				}

			}
		} else {
			return null;
		}
		return sb.toString();
	}
	
	public void clear(){
		this.whareClauses.clear();
	}

	private boolean existsInColumns(Object name, List[] tableColumns) {
		// if the name contains dot, it is column value
		//String str = (String) name;
		String str = name.toString();
		if (str.indexOf('.') >= 0) {
			return true;
		}
		for (int i = 0; i < tableColumns.length; i++) {
			List columns = tableColumns[i];
			if (columns.contains(name)) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	private List getColumns(String tableName) throws SQLException {
		String sql = "SHOW COLUMNS FROM " + tableName;
		List list = new ArrayList();

		ResultSet rs = this.connection.createStatement().executeQuery(sql);
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		return list;
	}
	
	/**
	 * fix escape characters " ' " and " \ ".
	 * @param str
	 * @return
	 */
	private String fixChars(String str){
		int pos = 0;
		while (pos < str.length()) {
			int index =  str.indexOf('\\', pos);
			if (index >= 0) {
				String sub = str.substring(0, index);
				sub = sub.concat("\\");
				sub = sub.concat(str.substring(index));
				str = sub;
				pos = index + 2;
			}
			else{
				break;
			}
		}
		pos = 0;
		while (pos < str.length()) {
			int index =  str.indexOf('\'', pos);
			if (index >= 0) {
				String sub = str.substring(0, index);
				sub = sub.concat("\'");
				sub = sub.concat(str.substring(index));
				str = sub;
				pos = index + 2;
			}
			else{
				break;
			}
		}
		//System.out.println(str);
		return str;
	}
}
